import { getDB } from '../../db';

/**
 * 文本标注管理   条件查询
 */
export const createQueryStatement = (queryParams: LX.DBService.TextMarkInfoQuery) => {
  const db = getDB();
  let query = `
     select * from (
			select
				v.id as v_id,v.save_path,1 type,v."video_name" as v_video_name,v."channel" as v_channel,v.start_time as v_start_time,
				m."id",m."start_time",m."end_time",m."label",m."describe",m."create_time"
			 from video_info v
					join video_mark_info m  on v.id = m.video_info_id and m.status = 0 and v.status = 0

				UNION	ALL

			SELECT
				v.id as v_id,v.save_path,2 type,v."video_name" as v_video_name,v."channel" as v_channel,v.start_time as v_start_time,
				m."id",m."start_time",m."end_time",m."label",m."describe",m."create_time"
			FROM
				video_info v
				JOIN video_real_mark_info m ON m.start_time >= v.start_time
				AND m.start_time <= v.end_time and m.status = 0 and v.channel = m.channel and v.status = 0
	  ) t
  `;

  const conditions: string[] = [];
  const params: any = {};

  if (queryParams.label !== undefined) {
    conditions.push('t.label LIKE @label');
    params['label'] = `%${queryParams.label}%`;
  }
  if (queryParams.videoName !== undefined) {
    conditions.push('t.v_video_name LIKE @videoName');
    params['videoName'] = `%${queryParams.videoName}%`;
  }
  if (queryParams.startTime) {
    conditions.push('t.create_time >= @startTime');
    params['startTime'] = queryParams.startTime;
  }
  if (queryParams.endTime) {
    conditions.push('t.create_time <= @endTime');
    params['endTime'] = queryParams.endTime;
  }

  if (conditions.length > 0) {
    query += ' WHERE ' + conditions.join(' AND ');
  }

  query += ' ORDER BY "create_time" DESC ';

  if (queryParams.current !== undefined && queryParams.page !== undefined) {
    query += ' LIMIT @current, @page';
    params['current'] = queryParams.current;
    params['page'] = queryParams.page;
  }
  return db.prepare(query).all(params);
};

/**
 * 文本标注管理   条件查询总条数
 */
export const createQueryStatementCount = (queryParams: LX.DBService.TextMarkInfoCountQuery) => {
  const db = getDB();
  let query = `
     select count(id) as count from (
			select
				v."video_name" as v_video_name,v."channel" as v_channel,
				m."id",m."start_time",m."end_time",m."label",m."describe",m."create_time"
			 from video_info v
					join video_mark_info m  on v.id = m.video_info_id and m.status = 0

				UNION	ALL

			SELECT
				v."video_name" as v_video_name,v."channel" as v_channel,
				m."id",m."start_time",m."end_time",m."label",m."describe",m."create_time"
			FROM
				video_info v
				JOIN video_real_mark_info m ON m.start_time >= v.start_time
				AND m.start_time <= v.end_time and m.status = 0 and v.channel = m.channel
	  ) t
  `;

  const conditions: string[] = [];
  const params: any = {};

  if (queryParams.label !== undefined) {
    conditions.push('t.label LIKE @label');
    params['label'] = `%${queryParams.label}%`;
  }
  if (queryParams.videoName !== undefined) {
    conditions.push('t.v_video_name LIKE @videoName');
    params['videoName'] = `%${queryParams.videoName}%`;
  }
  if (queryParams.startTime) {
    conditions.push('t.create_time >= @startTime');
    params['startTime'] = queryParams.startTime;
  }
  if (queryParams.endTime) {
    conditions.push('t.create_time <= @endTime');
    params['endTime'] = queryParams.endTime;
  }

  if (conditions.length > 0) {
    query += ' WHERE ' + conditions.join(' AND ');
  }

  return db.prepare(query).get(params);
};

/**
 * 文本标注管理    删除  同 删除视频标注
 */
export const createdDeleteTextMark = () => {
  const db = getDB();
  return db.prepare<[LX.DBService.textMarkUpdate]>(
    'UPDATE "main"."video_mark_info"  set "status" = @status where "id" = @id',
  );
};

export const createdDeleteTextRealMark = () => {
  const db = getDB();
  return db.prepare<[LX.DBService.textMarkUpdate]>(
    'UPDATE "main"."video_real_mark_info"  set "status" = @status where "id" = @id',
  );
};

/*多路播放，播放*/
export const createVideoListByIds = (ids: String) => {
  const db = getDB();
  let query = `
 SELECT "id", "video_name", "video_duration", "file_size", "start_time", "end_time", "channel", "status", "download_status", "save_path", "create_time", "create_user"
    FROM "main"."video_info" WHERE "status" = 0 and id in ( ${ids} )
`;
  console.log(query);
  return db.prepare(query).all();
};
